﻿using log4net;
using SISCON.Model;
using SISCON.Common;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Collections;

namespace SISCON.Dao
{
    /// <summary>
    /// Datta Access Object to handle connection to
    /// underlying database containing roles information
    /// </summary>
    public class RoleDAO : CommonDAO
    {
        private static ILog log = LogManager.GetLogger(typeof(RoleDAO));

        /// <summary>
        /// Obtiene un rol especifico de la base de datos
        /// </summary>
        /// <param name="roleId">Id del rol que se desea buscar</param>
        /// <returns>Role</returns>
        public Role GetRole(long roleId)
        {
            SqlCommand sqlCommand = new SqlCommand();
            SqlDataReader reader = null;
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection(_ConnStr);

                sqlCommand.CommandText = "SELECT * FROM sis_role sr, sis_role_det srd WHERE sr.role_id = srd.role_id AND sr.role_id = @roleId";
                sqlCommand.Connection = conn;
                sqlCommand.Parameters.Add(getNewParameter("@roleId", SqlDbType.BigInt, 0, roleId));
                sqlCommand.Prepare();

                conn.Open();

                reader = sqlCommand.ExecuteReader();

                Role role = null;

                while (reader.Read())
                {
                    if (role == null)
                    {
                        role = new Role();
                        role.RoleId = getReaderLong(reader, "role_id");
                        role.Name = getReaderString(reader, "name");
                        role.IsStatic = getReaderBool(reader, "is_static");
                        role.IsActive = getReaderBool(reader, "is_active");
                        role.CreationDate = getReaderDateTime(reader, "creation_date");
                        role.LastUpdateDate = getReaderDateTime(reader, "last_update_date");
                        role.Permissions = new List<RoleDet>();
                    }
                    RoleDet roleDet = new RoleDet();
                    roleDet.RoleDetId = getReaderLong(reader, "role_det_id");
                    roleDet.RoleId = getReaderLong(reader, "role_id");
                    roleDet.Resource = getReaderString(reader, "resource");
                    roleDet.AccessLevel = getReaderString(reader, "access_level");

                    role.Permissions.Add(roleDet);
                }

                return role;
            }
            catch (Exception exp)
            {
                log.Error("Error in GetRole()", exp);
                throw exp;
            }
            finally
            {
                if (reader != null)
                {
                    reader.Close();
                }
            }
        }

        /// <summary>
        /// Obtiene todos los roles es pecificados por roleNameFilter
        /// </summary>
        /// <param name="roleNameFilter">Filtro para traer roles dependiendo de su nombre</param>
        /// <returns>Lista de roles</returns>
        public List<Role> GetAllRoles(string roleNameFilter)
        {
            List<Role> LstRoles = new List<Role>();
            SqlDataReader reader = null;
            SqlCommand sqlCommand = new SqlCommand();

            if (roleNameFilter != "" && roleNameFilter != null)
            {
                roleNameFilter += "%";
            }

            try
            {
                sqlCommand.CommandText = " select r.role_id, r.name, r.is_static, r.is_active, r.created_by, r.creation_date, r.last_updated_by, r.last_update_date, " +
                                   " rd.role_det_id, rd.role_id, rd.resource, rd.access_level " +
                                   " from sis_role as r left outer join " +
                                   " sis_role_det as rd on (rd.role_id = r.role_id) " +
                                   (roleNameFilter != "" && roleNameFilter != null ? " where r.name like @roleNameFilter" : "") +
                                   " ORDER BY r.role_id ";
                 
                if (roleNameFilter != "" && roleNameFilter != null)
                {
                    sqlCommand.Parameters.Add(getNewParameter("@roleNameFilter", SqlDbType.NVarChar, 50, roleNameFilter));
                }

                sqlCommand.Connection = new SqlConnection(_ConnStr);
                sqlCommand.Connection.Open();
                reader = sqlCommand.ExecuteReader();

                long lastRoleId = -1;
                Role role = null;
                while (reader.Read())
                {
                    if (lastRoleId != (long)reader["role_id"])
                    {
                        lastRoleId = (long)reader["role_id"];
                        if (role != null)
                        {
                            LstRoles.Add(role);
                        }
                        role = new Role();
                        role.RoleId = getReaderLong(reader, "role_id");
                        role.Name = getReaderString(reader, "name");
                        role.IsStatic = getReaderBool(reader, "is_static");
                        role.IsActive = getReaderBool(reader, "is_active");
                        role.CreationDate = getReaderDateTime(reader, "creation_date");
                        role.LastUpdateDate = getReaderDateTime(reader, "last_update_date");
                        role.Permissions = new List<RoleDet>();
                    }
                    RoleDet roleDet = new RoleDet();
                    roleDet.RoleDetId = getReaderLong(reader, "role_det_id");
                    roleDet.RoleId = getReaderLong(reader, "role_id");
                    roleDet.Resource = getReaderString(reader, "resource");
                    roleDet.AccessLevel = getReaderString(reader, "access_level");

                    role.Permissions.Add(roleDet);
                }

                if (role != null)
                {
                    LstRoles.Add(role);
                }

                return LstRoles;
            }
            catch (Exception exp)
            {
                log.Error("Error in GetAllRoles()", exp);
                throw exp;
            }
            finally
            {
                if (reader != null && !reader.IsClosed)
                {
                    reader.Close();
                }
            }
        }

        /// <summary>
        /// Guarda un rol en la base de datos
        /// </summary>
        /// <param name="Role">Nombre del nuevo rol</param>
        /// <param name="Permissions">Permisos especificos para ese rol</param>
        public void SaveRole(Role Role, Dictionary<string, string> Permissions)
        {
            SqlConnection conn = null;
            SqlTransaction trx = null;
            string roleId = "";
            try
            {
                conn = new SqlConnection(_ConnStr);
                conn.Open();
                trx = conn.BeginTransaction();
                SqlCommand sqlCommand = new SqlCommand(null, conn, trx);
                if (Role.RoleId == 0)
                {
                    sqlCommand.CommandText = " insert into sis_role (name, is_static, is_active, created_by, creation_date, last_updated_by, last_update_date) values (" +
                                             "@name, 0, @isActive, 0, SYSDATETIME(), 0, SYSDATETIME());Select Scope_Identity()";
                    sqlCommand.Parameters.Add(getNewParameter("@name", SqlDbType.NVarChar, 80, Role.Name));
                    sqlCommand.Parameters.Add(getNewParameter("@isActive", SqlDbType.Bit, 0, (Role.IsActive ? 1 : 0)));

                    roleId = sqlCommand.ExecuteScalar().ToString();
                }
                else
                {
                    roleId = Role.RoleId.ToString();
                    sqlCommand.CommandText = "UPDATE sis_role set name = @name, is_active =  @isActive, last_update_date = sysdatetime() " +
                                             "WHERE role_id = @roleId";
                    sqlCommand.Parameters.Add(getNewParameter("@name", SqlDbType.NVarChar, 80, Role.Name));
                    sqlCommand.Parameters.Add(getNewParameter("@isActive", SqlDbType.Bit, 0, (Role.IsActive ? 1 : 0)));
                    sqlCommand.Parameters.Add(getNewParameter("@roleId", SqlDbType.BigInt, 0, roleId));

                    sqlCommand.ExecuteNonQuery();

                    sqlCommand = new SqlCommand(null, conn, trx);
                    sqlCommand.CommandText = "DELETE from sis_role_det WHERE role_id = @roleId";
                    sqlCommand.Parameters.Add(getNewParameter("@roleId", SqlDbType.BigInt, 0, roleId));
                    sqlCommand.ExecuteNonQuery();
                }

                for (int i = 0; i < Permissions.Count; i++)
                {
                    sqlCommand = new SqlCommand(null, conn, trx);
                    sqlCommand.CommandText = " insert into sis_role_det (role_id, resource, access_level) values (@roleId, @permissionKey, @permissionValue);";
                    sqlCommand.Parameters.Add(getNewParameter("@roleId", SqlDbType.BigInt, 0, roleId));
                    sqlCommand.Parameters.Add(getNewParameter("@permissionKey", SqlDbType.NVarChar, 80, Permissions.Keys.ToArray()[i]));
                    sqlCommand.Parameters.Add(getNewParameter("@permissionValue", SqlDbType.Char, 1, Permissions.Values.ToArray()[i]));
                    sqlCommand.ExecuteNonQuery();
                }
                trx.Commit();
            }
            catch (SqlException sqlEx)
            {
                if (sqlEx.Number == 2601)
                {
                    throw new SISCONException("El nombre del rol ya existe");
                }
                else
                {
                    log.Error("Error in SaveRole()", sqlEx);
                    throw new SISCONException("Error en la base de datos");
                }
            }
            catch (Exception exp)
            {
                log.Error("Error in SaveRole()", exp);
                throw new SISCONException("Error al tratar de guardar el rol");
            }
            finally
            {
                if (conn != null && conn.State != ConnectionState.Closed)
                {
                    conn.Close();
                }
            }

        }

        /// <summary>
        /// Obtiene los permisos para un usuario en especifico
        /// </summary>
        /// <param name="UserId">Id del usuario del que se quieren los roles</param>
        /// <returns>Dictionary</returns>
        public Dictionary<string, string> GetPermissionsForUser(String UserId)
        {
            SqlConnection conn = null;
            SqlDataReader reader = null;
            try
            {

                conn = new SqlConnection(_ConnStr);
                conn.Open();
                SqlCommand sqlCommand = new SqlCommand(null, conn);

                sqlCommand.CommandText = " select distinct rd.resource, rd.access_level " +
                                         " from sis_user as u left outer join " +
                                         " sis_role as r on r.role_id = u.user_id left outer join " +
                                         " sis_role_det as rd on rd.role_id = r.role_id " +
                                         " where (u.user_id = @userId)";

                sqlCommand.Parameters.Add(getNewParameter("@userId", SqlDbType.BigInt, 0, UserId));

                reader = sqlCommand.ExecuteReader();

                Dictionary<string, string> permissions = new Dictionary<string, string>();

                while (reader.Read())
                {
                    permissions.Add(reader["resource"].ToString(), reader["access_level"].ToString());
                }

                return permissions;

            }
            catch (Exception exp)
            {
                log.Error("Error in GetPermissionsForUser()", exp);
                throw exp;
            }
            finally
            {
                if (reader != null)
                {
                    reader.Close();
                }
                if (conn != null && conn.State != ConnectionState.Closed)
                {
                    conn.Close();
                }
            }

        }

        /// <summary>
        /// Elimina un rol especifico de la base de datos
        /// </summary>
        /// <param name="RoleId">Id del rol que se desea eliminar</param>
        public void DeleteRole(string RoleId)
        {
            SqlConnection conn = null;
            SqlTransaction trx = null;
            try
            {
                conn = new SqlConnection(_ConnStr);
                conn.Open();
                trx = conn.BeginTransaction();
                SqlCommand sqlCommand = new SqlCommand(null, conn, trx);
                sqlCommand.CommandText = " delete from sis_role_det where role_id = @roleId";
                sqlCommand.Parameters.Add(getNewParameter("@roleId", SqlDbType.BigInt, 0, RoleId));
                sqlCommand.ExecuteNonQuery();

                sqlCommand = new SqlCommand(null, conn, trx);
                sqlCommand.CommandText = " delete from sis_role where role_id = @roleId and is_static = 0";
                sqlCommand.Parameters.Add(getNewParameter("@roleId", SqlDbType.BigInt, 0, RoleId));
                int deleted = sqlCommand.ExecuteNonQuery();

                if (deleted == 0)
                {
                    trx.Rollback();
                    throw new SISCONException("Error al tratar de borrar el rol");
                }
                else
                {
                    trx.Commit();
                }

            }
            catch (SISCONException velEx)
            {
                throw velEx;
            }
            catch (Exception exp)
            {
                log.Error("Error in DeleteRole()", exp);
                throw new SISCONException("Error al tratar de borrar el rol");
            }
            finally
            {
                if (conn != null && conn.State != ConnectionState.Closed)
                {
                    conn.Close();
                }
            }
        }
    }
}